package Code;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.swing.JOptionPane;
import DatabaseCommands.DatabaseInfo;

public class Homework4{

	DatabaseInfo DInfo=DatabaseInfo.getInstance();
	private List<String> DAuthors=new ArrayList<String>();
	private List<String> Books=new ArrayList<String>();
	private List<Integer> BooksId=new ArrayList<Integer>();
	private List<String> Borrower=new ArrayList<String>();
	private List<Integer> BorrowerIds=new ArrayList<Integer>();
	private List<String> Branchs=new ArrayList<String>();
	private List<Integer> BranchIds=new ArrayList<Integer>();
	ResultSet Results;
	
	public Homework4(String Name){
		switch(Name){
			case"Return Author Books":
				ReturnBook();
			break;
			case"Change Book Title":
				ChangeBookTitle();
			break;
			case"Borrowed Books":
				ViewBorrowedBooks();
			break;
			case"Books Checked Out":
				BooksCheckedOut();
			break;
			default:
				return;
		}
		
	}
	
	
	
	public void ReturnBook(){
		try{
			Results=DInfo.GetConnection().createStatement().executeQuery("Select * From BookAuthors Group By AuthorName Having Count(*)>=1");
			while(Results.next()){
				DAuthors.add(Results.getString(2));
			}
		}catch(Exception e){
			JOptionPane.showMessageDialog(null,"Error getting Authors.");
		}
		
		
		String Author=(String)JOptionPane.showInputDialog(null,"Choose Author:\n","Return Books For Author",JOptionPane.PLAIN_MESSAGE,null,DAuthors.toArray(),null);

		    
		if(Author.length()<=0){return;}
		try{
			DInfo.GetConnection().createStatement().execute("Delete BL.* From BookLoans BL Inner Join BookAuthors BA on BL.BookId=BA.BookId Where BA.AuthorName='"+Author+"'");
			JOptionPane.showMessageDialog(null,"Books by '"+Author+"' returned.");
		}catch(Exception e){
			JOptionPane.showMessageDialog(null,"Error returning books.");
		}
	}
	
	
	
	public void ChangeBookTitle(){
		int BookId=-1;
		try{
			Results=DInfo.GetConnection().createStatement().executeQuery("Select * From Book");
			while(Results.next()){
				BooksId.add(Results.getInt(1));
				Books.add(Results.getString(2));
			}
		}catch(Exception e){
			JOptionPane.showMessageDialog(null,"Error getting Books.");
		}
		
		
		String Book=(String)JOptionPane.showInputDialog(null,"Choose Book:\n","Change Book Title",JOptionPane.PLAIN_MESSAGE,null,Books.toArray(),null);
		String NewBook=JOptionPane.showInputDialog("New Title:");

		for(int x=0;x<Books.size();x++){
			if(Books.get(x)==Book){
				BookId=BooksId.get(x);
			}
		}
		
		if(((Book!=null)&&(Book.length()>0))&&((NewBook!=null)&&(NewBook.length()>0))){
			try{
				DInfo.GetConnection().createStatement().execute("Update Book Set Title='"+NewBook+"' Where BookId="+BookId);
				JOptionPane.showMessageDialog(null,"Book title '"+Book+"' changed to '"+NewBook);
			}catch(Exception e){
				JOptionPane.showMessageDialog(null,"Error Changing Book Title.");
			}
		}
	}
	
	
	
	public void ViewBorrowedBooks(){
		int BorrowerId=-1;
		try{
			Results=DInfo.GetConnection().createStatement().executeQuery("Select * From Borrower");
			while(Results.next()){
				BorrowerIds.add(Results.getInt(1));
				Borrower.add(Results.getString(2));
			}
		}catch(Exception e){
			JOptionPane.showMessageDialog(null,"Error getting Borrowers.");
		}
		
		
		String BorrowerName=(String)JOptionPane.showInputDialog(null,"Choose Borrower:\n","View Borrower Books",JOptionPane.PLAIN_MESSAGE,null,Borrower.toArray(),null);

		for(int x=0;x<Borrower.size();x++){
			if(Borrower.get(x)==BorrowerName){
				BorrowerId=BorrowerIds.get(x);
			}
		}
		
		if(((BorrowerName!=null)&&(BorrowerName.length()>0))){
			try{
				Results=DInfo.GetConnection().createStatement().executeQuery("Select Distinct BA.AuthorName From BookLoans BL join BookAuthors BA On BL.BookId=BA.BookId Where BL.CardNumber="+BorrowerId);
				String Message="";
				while(Results.next()){
					Message+=Results.getString(1)+"\n";
				}
				JOptionPane.showMessageDialog(null,Message);
			}catch(Exception e){
				JOptionPane.showMessageDialog(null,"Error getting borrowed books.");
			}
		}
	}


	
	public void BooksCheckedOut(){
		int BranchId=-1;
		try{
			Results=DInfo.GetConnection().createStatement().executeQuery("Select * From LibraryBranch");
			while(Results.next()){
				BranchIds.add(Results.getInt(1));
				Branchs.add(Results.getString(2));
			}
		}catch(Exception e){
			JOptionPane.showMessageDialog(null,"Error getting Branches.");
		}
		
		
		String BranchName=(String)JOptionPane.showInputDialog(null,"Choose Branch:\n","View Branch Checkouts",JOptionPane.PLAIN_MESSAGE,null,Branchs.toArray(),null);
		String StartDate=JOptionPane.showInputDialog("Start Date(YYYY-MM-DD):");
		String EndDate=JOptionPane.showInputDialog("End Date(YYYY-MM-DD):");

		for(int x=0;x<Branchs.size();x++){
			if(Branchs.get(x)==BranchName){
				BranchId=BranchIds.get(x);
			}
		}
		
		if(((BranchName!=null)&&(BranchName.length()>0))){
			try{
				Results=DInfo.GetConnection().createStatement().executeQuery("Select B.Title From BookLoans BL join Book B On BL.BookId=B.BookId Where BL.BranchId="+BranchId+" AND DateOut Between '"+StartDate+"' and '"+EndDate+"'");
				String Message="";
				while(Results.next()){
					Message+=Results.getString(1)+"\n";
				}
				JOptionPane.showMessageDialog(null,Message);
			}catch(Exception e){
				JOptionPane.showMessageDialog(null,"Error getting checked out books.");
			}
		}
	}
	
	
}
